package dao

import (
	"context"
	"fmt"
	"github.com/pkg/errors"
	"gorm.io/gorm"
	"gorm.io/gorm/clause"
	"inventory/api/inventory"
	"inventory/internal/constant"
	"inventory/internal/model"
	"inventory/pkg/ecode"
	"inventory/pkg/util"
	"strconv"
	"strings"
	"time"
)

// FindWarehouses 按照特定条件获取仓库列表
func (d *Dao) FindWarehouses(ctx context.Context, sgID, pStoreID uint32, pageNo, pageSize int) (total int64, warehouses []*model.Warehouse, err error) {
	var db *gorm.DB
	if pStoreID == 0 {
		// 总部模式不需要过滤门店仓
		db = d.TransContext(ctx).Model(&model.Warehouse{}).Where("sg_id = ? and status != ?", sgID, pStoreID, constant.WarehouseDeleted)
	} else {
		db = d.TransContext(ctx).Model(&model.Warehouse{}).Where("store_id = ? and sg_id = ? and status != ?", pStoreID, sgID, constant.WarehouseDeleted)
	}
	if pageNo != 0 {
		// 分页获取
		err = db.Count(&total).Error
		if err == nil {
			err = db.Order("id asc").Offset((pageNo - 1) * pageSize).Limit(pageSize).Find(&warehouses).Error
		}
	} else {
		// 不分页
		err = db.Order("id asc").Find(&warehouses).Error
		if err == nil {
			total = int64(len(warehouses))
		}
	}
	err = d.processSqlError(err)
	return
}

// FindWarehouseStores 按照特定条件获取仓库列表
func (d *Dao) FindWarehouseStores(ctx context.Context, sgID uint32) (
	warehouses []*model.Warehouse, err error) {
	db := d.TransContext(ctx).Model(&model.Warehouse{}).Where(
		"sg_id = ? and store_id != 0 and status = ?", sgID, constant.WarehouseEnable)
	err = db.Find(&warehouses).Error
	if err != nil {
		err = d.processSqlError(err)
	}
	return
}

// GetWarehouseInfo 从db获取指定仓库
func (d *Dao) GetWarehouseInfo(ctx context.Context, sgID, storeID, id uint32) (warehouse *model.Warehouse, err error) {
	warehouse = &model.Warehouse{}
	if storeID == 0 {
		err = d.TransContext(ctx).Where("id = ? and sg_id = ?", id, sgID).Take(warehouse).Error
	} else {
		err = d.TransContext(ctx).Where("id = ? and sg_id = ? and store_id = ?", id, sgID, storeID).Take(warehouse).Error
	}
	if err != nil {
		err = d.processSqlError(err)
	}
	err = d.CheckWarehouseStatus(warehouse)
	return
}

// 检查仓库合法性
func (d *Dao) CheckWarehouseStatus(warehouse *model.Warehouse) error {
	if warehouse.Status != constant.WarehouseEnable {
		switch warehouse.Status {
		case constant.WarehouseDeleted:
			return ecode.BadRequest("所选仓库已删除")
		case constant.WarehouseDisable:
			return ecode.BadRequest("所选仓库已停用")
		case constant.WarehouseAbnormal:
			return ecode.BadRequest("所选门店仓地址&经纬度&城市编码等信息异常，请在门店详情页重新进行地图选点保存再使用")
		}
	}
	return nil
}

// GetWarehouseInfoInternal rpc从db获取指定仓库
// NOTE: 未校验商户 门店 仅供内部调用
func (d *Dao) GetWarehouseInfoInternal(ctx context.Context, id uint32) (warehouse *model.Warehouse, err error) {
	warehouse = &model.Warehouse{}
	err = d.TransContext(ctx).Where("id = ?", id, constant.WarehouseEnable).Take(warehouse, id).Error
	if err != nil {
		err = d.processSqlError(err)
	}
	err = d.CheckWarehouseStatus(warehouse)
	return
}

// GetWarehouseInfoByStoreID rpc从db获取指定仓库
// NOTE: 未校验商户 门店 仅供内部调用
func (d *Dao) GetWarehouseInfoByStoreID(ctx context.Context, storeID uint32) (warehouse *model.Warehouse, err error) {
	warehouse = &model.Warehouse{}
	err = d.TransContext(ctx).Where("store_id = ?", storeID).Take(warehouse).Error
	if err != nil {
		err = d.processSqlError(err)
	}
	return
}

// GetWarehouseInfoByName 根据名称查询仓库
func (d *Dao) GetWarehouseInfoByName(ctx context.Context, sgID uint32, name string) (warehouse *model.Warehouse, err error) {
	warehouse = &model.Warehouse{}
	err = d.TransContext(ctx).Where("sg_id = ? and status != ? and name = ?", sgID, constant.WarehouseDeleted, name).Take(warehouse).Error
	if err != nil {
		err = d.processSqlError(err)
	}
	return
}

// LockWarehouse 锁定指定仓库
func (d *Dao) LockWarehouse(ctx context.Context, id, sgID uint32) (warehouse *model.Warehouse, err error) {
	warehouse = &model.Warehouse{}
	err = d.TransContext(ctx).Clauses(clause.Locking{Strength: "UPDATE"}).
		Where("sg_id = ? and id = ?", sgID, id).Take(warehouse).Error
	return
}

// UpdateBomStorageMode
func (d *Dao) UpdateBomStorageMode(ctx context.Context, warehouseID uint32, storageMode uint8) (err error) {
	err = d.TransContext(ctx).Model(&model.Storage{}).Where("warehouse_id = ?", warehouseID).Update(
		"storage_mode", storageMode).Error
	return
}

// FindWarehouseCityExpresses 按照特定条件获取仓库同城配送列表
func (d *Dao) FindWarehouseCityExpresses(ctx context.Context, sgId uint32, req *inventory.CommonPaginateRequest) (total int64, views []*inventory.ExpressView, err error) {
	var warehouses []*model.Warehouse
	db := d.TransContext(ctx).Model(&model.Warehouse{}).Where("sg_id = ? and status = ? and city_express = true",
		sgId, constant.WarehouseEnable)
	if req.PStoreId != 0 {
		db = db.Where("store_id = ?", req.PStoreId)
	}

	if err = db.Count(&total).Error; err != nil {
		err = errors.Wrapf(err, "FindWarehouseCityExpresses Count")
		return
	}
	err = db.Order("id asc").Offset(int((req.PageNo - 1) * req.PageSize)).Limit(int(req.PageSize)).Find(&warehouses).Error
	if err != nil {
		err = errors.Wrapf(err, "FindWarehouseCityExpresses")
		return
	}
	warehouseIDs := make([]uint32, len(warehouses))
	for i, w := range warehouses {
		warehouseIDs[i] = w.ID
	}

	var expressPlatforms []*model.ExpressPlatform
	err = d.TransContext(ctx).Model(&model.ExpressPlatform{}).Where("mode = 1").Find(&expressPlatforms).Error
	if err != nil {
		err = errors.Wrapf(err, "FindWarehouseCityExpresses")
		return
	}
	platformIDs := make([]uint32, len(expressPlatforms))
	platformNameMap := make(map[uint32]string)
	for i, p := range expressPlatforms {
		platformIDs[i] = p.ID
		platformNameMap[p.ID] = p.Name
	}

	var expressConfigs []*model.ExpressConfig
	err = d.TransContext(ctx).Model(
		&model.ExpressConfig{}).Where(
		"warehouse_id in ? and platform_id in ? and status = ?", warehouseIDs, platformIDs, constant.ExpressConfigured).Find(&expressConfigs).Error
	if err != nil {
		err = errors.Wrapf(err, "FindWarehouseCityExpresses")
		return
	}

	warehouseExpressMap := make(map[uint32][]string)
	for _, c := range expressConfigs {
		warehouseExpressMap[c.WarehouseID] = append(warehouseExpressMap[c.WarehouseID], platformNameMap[c.PlatformID])
	}
	var regionWarehouseIdMap util.UInt32BoolMap
	if regionWarehouseIdMap, err = d.FindExpressRegionsWarehouseIDsByWarehouseIDs(ctx, warehouseIDs); err != nil {
		return
	}
	views = make([]*inventory.ExpressView, len(warehouses))
	for i, w := range warehouses {
		views[i] = &inventory.ExpressView{
			Id:        w.ID,
			Name:      w.Name,
			StoreId:   w.StoreID,
			Platforms: strings.Join(warehouseExpressMap[w.ID], ","),
		}
		if _, ok := regionWarehouseIdMap[w.ID]; ok {
			views[i].RegionStatus = 1
		}
	}
	return
}

// FindWarehouseCityExpressPlatform 指定仓库同城配送平台列表
func (d *Dao) FindWarehouseCityExpressPlatform(ctx context.Context, sgId uint32, req *inventory.CommonObjectRequest) ([]*inventory.WarehouseCityExpressPlatformView, error) {
	warehouse := &model.Warehouse{SgID: sgId}
	if req.PStoreId != 0 {
		warehouse.StoreID = req.PStoreId
	}
	if err := d.TransContext(ctx).Where(warehouse).Take(&model.Warehouse{ID: req.Id}).Error; err != nil {
		// 越权操作
		return nil, d.processSqlError(err)
	}

	var views []*inventory.WarehouseCityExpressPlatformView
	err := d.TransContext(ctx).Model(&model.ExpressConfig{}).Select(
		"inv_express_platform.id, inv_express_platform.name, inv_express_config.info,"+
			" inv_express_platform.config_type, inv_express_config.status").Joins(
		"JOIN inv_express_platform ON inv_express_platform.id = inv_express_config.platform_id AND inv_express_platform.mode = 1",
	).Where("inv_express_config.warehouse_id = ?", req.Id).Scan(&views).Error
	if err != nil {
		return nil, d.processSqlError(err)
	}
	return views, nil
}

// LockExpressConfigByMode 根据mode锁定记录
func (d *Dao) LockExpressConfigByMode(ctx context.Context, sgID, id, platformID uint32, mode int) (config *model.ExpressConfig, err error) {
	config = &model.ExpressConfig{}
	err = d.TransContext(ctx).Model(&model.ExpressConfig{}).Joins(
		"JOIN inv_express_platform ON inv_express_platform.id = inv_express_config.platform_id "+
			"AND inv_express_platform.mode = ?", mode).Clauses(clause.Locking{Strength: "UPDATE"}).
		Where("inv_express_config.sg_id = ?", sgID).Where(
		"inv_express_config.warehouse_id = ? and inv_express_config.platform_id = ?", id, platformID).Take(config).Error
	if err != nil {
		err = d.processSqlError(err)
		return
	}
	return
}

// SaveExpressConfig 保存配送平台信息
func (d *Dao) SaveExpressConfig(ctx context.Context, config *model.ExpressConfig) (err error) {
	err = d.TransContext(ctx).Save(config).Error
	if err != nil {
		err = d.processSqlError(err)
	}
	return
}

// FindWarehouseExpressCoordinates 获取同城配送区域
func (d *Dao) FindWarehouseExpressCoordinates(ctx context.Context, warehouseID uint32) (regions []*model.ExpressCoordinates, err error) {
	err = d.TransContext(ctx).Where("warehouse_id = ?", warehouseID).Find(&regions).Error
	if err != nil {
		err = d.processSqlError(err)
	}
	return
}

// FindWarehouseOrdinaryExpress 仓库普通快递列表
func (d *Dao) FindWarehouseOrdinaryExpress(ctx context.Context, sgId uint32, req *inventory.CommonPaginateRequest) (
	total int64, views []*inventory.ExpressView, err error) {
	var warehouses []*model.Warehouse
	db := d.TransContext(ctx).Model(&model.Warehouse{}).Where("sg_id = ? and status = ? and ordinary_express = true",
		sgId, constant.WarehouseEnable)
	if req.PStoreId != 0 {
		db = db.Where("store_id = ?", req.PStoreId)
	}
	if err = db.Count(&total).Error; err != nil {
		err = d.processSqlError(err)
		return
	}
	err = db.Order("id asc").Offset(int((req.PageNo - 1) * req.PageSize)).Limit(int(req.PageSize)).Find(&warehouses).Error
	if err != nil {
		err = d.processSqlError(err)
		return
	}
	warehouseIDs := make([]uint32, len(warehouses))
	for i, w := range warehouses {
		warehouseIDs[i] = w.ID
	}
	err = d.TransContext(ctx).Raw(`
SELECT t.id, t.name, t.store_id, COALESCE(t.platforms, '') as platforms, case when c.index is NULL then 0 else 1 end as region_status FROM
(SELECT w.id, w.name, w.store_id, string_agg(DISTINCT p.name, ',') AS platforms 
FROM inv_warehouse w LEFT OUTER JOIN inv_express_config e ON e.warehouse_id = w.id 
LEFT OUTER JOIN inv_express_platform p ON p.id = e.platform_id AND p.mode = ?
WHERE w.id IN ? GROUP by w.id) t left outer JOIN inv_express_city c ON t.id=c.warehouse_id and c.index = 0 
WHERE t.id IN ?`, int(inventory.DeliverType_ORDINARY), warehouseIDs, warehouseIDs).Scan(&views).Error
	if err != nil {
		err = d.processSqlError(err)
		return
	}
	return
}

// DeleteAllExpressCoordinatesByWarehouseID 删除指定仓库下所有同城配送配送区域
func (d *Dao) DeleteAllExpressCoordinatesByWarehouseID(ctx context.Context, warehouseID uint32) (err error) {
	err = d.TransContext(ctx).Delete(&model.ExpressCoordinates{}, "warehouse_id = ?", warehouseID).Error
	if err != nil {
		err = d.processSqlError(err)
	}
	return
}

// AddWarehouseExpressCoordinates 新增配送区域
func (d *Dao) AddWarehouseExpressCoordinates(ctx context.Context, coordinates *model.ExpressCoordinates) (err error) {
	err = d.TransContext(ctx).Create(coordinates).Error
	if err != nil {
		err = d.processSqlError(err)
	}
	return
}

// DeleteAllExpressCity 删除指定仓库下所有普通快递配送区域
func (d *Dao) DeleteAllExpressCity(ctx context.Context, warehouseID uint32) (err error) {
	err = d.TransContext(ctx).Delete(&model.ExpressCity{}, "warehouse_id = ?", warehouseID).Error
	if err != nil {
		err = d.processSqlError(err)
	}
	return
}

// AddWarehouseExpressCity 新增普通快递配送区域
func (d *Dao) AddWarehouseExpressCity(ctx context.Context, cities *model.ExpressCity) (err error) {
	err = d.TransContext(ctx).Create(cities).Error
	if err != nil {
		err = d.processSqlError(err)
	}
	return

}

// DeleteAllExpressPlatformByWarehouseID 删除指定仓库下所有配送平台配置
func (d *Dao) DeleteAllExpressPlatformByWarehouseID(ctx context.Context, warehouseID, operator uint32) (err error) {
	err = d.TransContext(ctx).Model(&model.ExpressConfig{}).Joins(
		"JOIN inv_express_platform on inv_express_platform.id = inv_express_config.platform_id").Where(
		"warehouse_id = ? AND status = ?", warehouseID, constant.ExpressConfigured).Updates(map[string]interface{}{
		"status":      constant.ExpressUnbound,
		"operator":    operator,
		"update_time": time.Now(),
	}).Error
	err = d.processSqlError(err)
	return
}

// FindPlatformIDsByMode 查找配送平台根据mode
func (d *Dao) FindPlatformIDsByMode(ctx context.Context, mode int) (PlatformIDs []int, err error) {
	err = d.TransContext(ctx).Model(&model.ExpressPlatform{}).Where("mode = ?", mode).Pluck("id", &PlatformIDs).Error
	if err != nil {
		err = d.processSqlError(err)
	}
	return
}

// FindPlatformByMode 查找配送平台根据mode
func (d *Dao) FindPlatformByMode(ctx context.Context, mode int) (platforms []*model.ExpressPlatform, err error) {
	err = d.TransContext(ctx).Model(&model.ExpressPlatform{}).Where("mode = ?", mode).Find(&platforms).Error
	if err != nil {
		err = d.processSqlError(err)
	}
	return
}

// AddWarehouseExpressPlatform 新增仓库配送平台
func (d *Dao) AddWarehouseExpressPlatform(ctx context.Context, config *model.ExpressConfig) (err error) {
	err = d.TransContext(ctx).Clauses(clause.OnConflict{
		Columns: []clause.Column{{Name: "warehouse_id"}, {Name: "platform_id"}},
		DoUpdates: clause.Assignments(map[string]interface{}{
			"status":      config.Status,
			"update_time": config.UpdateTime}),
	}).Create(config).Error
	if err != nil {
		err = d.processSqlError(err)
	}
	return
}

// FindWarehousesExpressCities 查找仓库可配送城市
func (d *Dao) FindWarehousesExpressCities(ctx context.Context, warehouseID uint32) (cities []*model.ExpressCity, err error) {
	err = d.TransContext(ctx).Where("warehouse_id = ?", warehouseID).Find(&cities).Error
	if err != nil {
		err = d.processSqlError(err)
	}
	return
}

// FindWarehousesPlatforms 查找仓库下平台列表
func (d *Dao) FindWarehousesPlatforms(ctx context.Context, warehouseID uint32, deliverType uint8) (platforms []*inventory.PlatformView, err error) {
	err = d.TransContext(ctx).Model(&model.ExpressConfig{}).Select(
		"inv_express_platform.id, inv_express_platform.name").Joins(
		"JOIN inv_express_platform ON inv_express_platform.id = inv_express_config.platform_id").Where(
		"warehouse_id = ? and mode = ?", warehouseID, deliverType).Find(&platforms).Error
	if err != nil {
		err = d.processSqlError(err)
	}
	return
}

// GetWarehouseStorageTypes 获取物料实时仓库可选类别
func (d *Dao) GetWarehouseStorageTypes(ctx context.Context, sgID, warehouseID uint32) (views []*inventory.WarehouseStorageType, err error) {
	err = d.TransContext(ctx).Raw(`
SELECT t.id, CONCAT(tt.name, '/', t.name) as name 
FROM inv_storage AS s 
JOIN inv_material AS m ON m.id = s.material_id 
JOIN inv_material_type AS t ON t.id = m.mt_id 
JOIN inv_material_type AS tt ON t.parent_id = tt.id  
WHERE m.status =? AND t.status=? AND t.sg_id = ? AND s.warehouse_id = ? GROUP BY t.id, tt.id`,
		constant.MaterialValid, constant.MaterialTypeValid, sgID, warehouseID).Scan(&views).Error
	if err != nil {
		err = d.processSqlError(err)
	}
	return
}

// GetWarehouseProductStorageTypes 获取商品实时仓库可选类别
func (d *Dao) GetWarehouseProductStorageTypes(ctx context.Context, empId, sgId, warehouseID uint32) (
	prodTypeIds []uint32, err error) {
	var prodTypes []*struct {
		TypeID uint32 `gorm:"column:type_id"`
	}
	err = d.TransContext(ctx).Model(&model.Storage{}).Select("distinct remark->'type_id' as type_id").Where(
		"warehouse_id = ? and storage_type = ?", warehouseID, int(inventory.StorageType_PROD)).Scan(&prodTypes).Error
	if err != nil {
		err = d.processSqlError(err)
		return
	}
	if len(prodTypes) != 0 {
		productTypeMap := make(util.UInt32BoolMap)
		for _, prodType := range prodTypes {
			productTypeMap[prodType.TypeID] = true
		}
		prodTypeIds = productTypeMap.GetKeys()
	}
	return
}

// FindWarehousesByIDs 根据ID列表获取仓库
func (d *Dao) FindWarehousesByIDs(ctx context.Context, warehouseIDs []uint32, sgID uint32) (warehouses []*model.Warehouse, err error) {
	err = d.TransContext(ctx).Model(&model.Warehouse{}).Where("id in ? and sg_id = ?",
		warehouseIDs, sgID).Find(&warehouses).Error
	if err != nil {
		err = d.processSqlError(err)
		return
	}
	if len(warehouseIDs) != len(warehouses) {
		warehouseMap := make(model.WarehouseMap)
		_ = warehouseMap.InitFromWarehouseArray(warehouses, sgID, false)
		for _, warehouseID := range warehouseIDs {
			err = ecode.BadRequest("仓库ID:%d不存在", warehouseID)
			return
		}
	}
	return
}

// FindWarehousesByStoreIDs 根据storeID列表获取仓库
func (d *Dao) FindWarehousesByStoreIDs(ctx context.Context, storeIDs []uint32) (warehouses []*model.Warehouse, err error) {
	err = d.TransContext(ctx).Model(&model.Warehouse{}).Where("store_id in ?", storeIDs).Find(&warehouses).Error
	if err != nil {
		err = d.processSqlError(err)
	}
	return
}

// FindWarehousesByIDsAndDeliverType 根据ID列表和配送方式获取仓库
func (d *Dao) FindWarehousesByIDsAndDeliverType(ctx context.Context, warehouseIDs []uint32,
	deliveryType inventory.DeliverType) (warehouses []*model.Warehouse, err error) {
	db := d.TransContext(ctx).Model(&model.Warehouse{}).Where("id in ? ", warehouseIDs)
	switch deliveryType {
	case inventory.DeliverType_CITY:
		db = db.Where("city_express = ?", true)
	case inventory.DeliverType_ORDINARY:
		db = db.Where("ordinary_express = ?", true)
	case inventory.DeliverType_PICK_UP:
		db = db.Where("pick_up = ?", true)
	default:
		err = ecode.BadRequest("配送方式不合法: delivery_type=%d", deliveryType)
		return
	}
	err = db.Find(&warehouses).Error
	if err != nil {
		err = d.processSqlError(err)
	}
	return
}

// FindAllWarehousesBySgID 匹配总部仓库
func (d *Dao) FindAllHQWarehousesBySgID(ctx context.Context, sgID uint32) (warehouse []*model.Warehouse, err error) {
	db := d.TransContext(ctx).Model(&model.Warehouse{}).Where("sg_id = ? AND store_id = 0 and status = ?",
		sgID, constant.WarehouseEnable)
	err = db.Find(&warehouse).Error
	if err != nil {
		err = d.processSqlError(err)
	}
	return
}

// FindExpressRegionsByWarehouseIDs 获取同城配送区域
func (d *Dao) FindExpressRegionsByWarehouseIDs(ctx context.Context, sgID, cityCode uint32, warehouseIDs []uint32) (
	regions []*model.ExpressCoordinates, err error) {
	err = d.TransContext(ctx).Model(&model.ExpressCoordinates{}).Where(
		"sg_id = ? and city_code = ? and status = ? and warehouse_id in ?",
		sgID, cityCode%100, true, warehouseIDs).Find(&regions).Error
	if err != nil {
		err = d.processSqlError(err)
	}
	return
}

// FindExpressRegionsWarehouseIDsByWarehouseIDs 获取同城配送区域已配置过的门店列表
func (d *Dao) FindExpressRegionsWarehouseIDsByWarehouseIDs(ctx context.Context, warehouseIDs []uint32) (warehouseIdMap util.UInt32BoolMap, err error) {
	var views []struct {
		WarehouseID uint32 `json:"warehouse_id"`
	}
	err = d.TransContext(ctx).Model(&model.ExpressCoordinates{}).Select("warehouse_id").Where(
		"warehouse_id in ?", warehouseIDs).Group("warehouse_id").Scan(&views).Error
	if err != nil {
		err = d.processSqlError(err)
	}
	warehouseIdMap = make(util.UInt32BoolMap)
	for _, v := range views {
		warehouseIdMap[v.WarehouseID] = true
	}
	return
}

// FindExpressCitiesByCityCodes 根据城市编码匹配
func (d *Dao) FindExpressCitiesByCityCodes(ctx context.Context, sgID uint32, cityCodes, warehouseIds []uint32) (
	expressCities []*model.ExpressCity, err error) {
	if cityCodes == nil {
		err = fmt.Errorf("cityCodes error")
		return
	}
	var buf strings.Builder
	buf.WriteString("{")
	for i, code := range cityCodes {
		if i > 0 {
			buf.WriteString(",")
		}
		buf.WriteString(strconv.Itoa(int(code)))
	}
	buf.WriteString("}")

	err = d.TransContext(ctx).Model(&model.ExpressCity{}).Where("sg_id = ? and  city_codes @>? and warehouse_id in ?",
		sgID, buf.String(), warehouseIds).Find(&expressCities).Error
	if err != nil {
		err = d.processSqlError(err)
	}
	return
}

// FindExpressCitiesByCityCodesFilterWarehouseIds 在指定仓库范围内根据城市编码匹配
func (d *Dao) FindExpressCitiesByCityCodesFilterWarehouseIds(ctx context.Context, sgID uint32,
	cityCodes, warehouseIds []uint32) (expressCities []*model.ExpressCity, err error) {
	var buf strings.Builder
	buf.WriteString("{")
	for i, code := range cityCodes {
		if i > 0 {
			buf.WriteString(",")
		}
		buf.WriteString(strconv.Itoa(int(code)))
	}
	buf.WriteString("}")
	err = d.TransContext(ctx).Model(&model.ExpressCity{}).Where("sg_id = ? and  city_codes @>? and warehouse_id in ?", sgID, buf.String(), warehouseIds).Find(&expressCities).Error
	if err != nil {
		err = d.processSqlError(err)
	}
	return
}

// GetExpressPlatform 获取指定配送平台信息
func (d *Dao) GetExpressPlatform(ctx context.Context, platformID uint32) (platform *model.ExpressPlatform, err error) {
	platform = &model.ExpressPlatform{}
	err = d.TransContext(ctx).Model(&model.ExpressPlatform{}).Where("id = ?", platformID).Take(platform, platformID).Error
	if err != nil {
		err = d.processSqlError(err)
	}
	return
}

// LockCalculatedProcessRequest 获取计算履约请求
func (d *Dao) LockCalculatedProcessRequest(ctx context.Context, requestID string, source uint8) (request *model.ProcessRequest, err error) {
	request = &model.ProcessRequest{}
	err = d.TransContext(ctx).Model(&model.ProcessRequest{}).Clauses(clause.Locking{Strength: "UPDATE"}).Where(
		"request_id = ? and source = ?", requestID, source).Take(request).Error
	if err != nil {
		err = d.processSqlError(err)
	}
	return
}

// GetExpressConfigByPlatformID 获取指定平台配置文件
func (d *Dao) GetExpressConfigByPlatformID(ctx context.Context, sgID, warehouseID, platformID uint32) (
	config *model.ExpressConfig, err error) {
	config = &model.ExpressConfig{}
	err = d.TransContext(ctx).Model(&model.ExpressConfig{}).Where("sg_id = ? and warehouse_id = ? and platform_id = ? and status = ?",
		sgID, warehouseID, platformID, constant.ExpressConfigured).Take(config).Error
	if err != nil {
		err = d.processSqlError(err)
	}
	return
}

// FindCanNotDeleteProducts 获取可删除的产品列表
func (d *Dao) FindCanNotDeleteProducts(ctx context.Context, productIDs []uint64) (storages []*model.Storage, err error) {
	err = d.TransContext(ctx).Model(&model.Storage{}).Where("prod_id in ? and (freeze_quantity != 0 or quantity != 0)", productIDs).Find(&storages).Error
	return
}

// FindBomByStorageKeys 通过storage_key 获取bom
func (d *Dao) FindBomByStorageKeys(ctx context.Context, storageKeys []string) (bom []*model.BomConfig, err error) {
	if len(storageKeys) == 0 {
		bom = []*model.BomConfig{}
		return
	}
	err = d.TransContext(ctx).Model(&model.BomConfig{}).Where("storage_key in ? and status = 1", storageKeys).Find(&bom).Error
	return
}

// FindBomByMaterialIds 通过material_id 获取bom
func (d *Dao) FindBomByMaterialIds(ctx context.Context, materialIds []uint64) (boms []*model.BomConfig, err error) {
	err = d.TransContext(ctx).Model(&model.BomConfig{}).Where("material_id in ? and status = 1", materialIds).Find(&boms).Error
	return
}

// FindBomByRefIds ref_id 获取bom
func (d *Dao) FindBomByRefIds(ctx context.Context, sgID uint32, refIds []uint64) (boms []*model.BomConfig, err error) {
	err = d.TransContext(ctx).Model(&model.BomConfig{}).Where("sg_id = ? and status = 1 and ref_id in ?",
		sgID, refIds).Find(&boms).Error
	return
}

// DeleteBomByStorageKey 删除bom配置
func (d *Dao) DeleteBomByStorageKey(ctx context.Context, sgID uint32, storageKey string, operator uint32,
	updateTime time.Time) (err error) {
	err = d.processSqlError(d.TransContext(ctx).Model(&model.BomConfig{}).Where(
		"storage_key = ? and status = 1 and sg_id = ?", storageKey, sgID).Updates(map[string]interface{}{
		"status":      0,
		"operator":    operator,
		"update_time": updateTime,
	}).Error)
	return
}

// UpsertBom 新建或更新bom
func (d *Dao) UpsertBom(ctx context.Context, b *model.BomConfig) (err error) {
	return d.TransContext(ctx).Clauses(clause.OnConflict{
		Columns:   []clause.Column{{Name: "storage_key"}, {Name: "material_id"}},
		DoUpdates: clause.AssignmentColumns([]string{"quantity", "status", "operator", "update_time"}),
	}).Create(&b).Error
}
